﻿using System;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using SAC.sqlHelper;
using SAC.Elink;
using SAC.Helper;
using System.Collections;
using System.Data;
using SAC.Power;
using SAC.Plink;
using SAC.DB2;

namespace DAL
{
    public class DALRole
    {
        string rlDBType = "";
        string rtDBType = "";

        string pGl1 = "WHSIS.U1APSH.U1A04013";
        string pGl2 = "WHSIS.U2APSH.U2A04013";

        public DataTable GetAllRole(out string errMsg)
        {
            this.init();
            errMsg = "";

            string sql1 = "select min(ID_KEY) as ID_KEY,UGROUP from WebUser group by UGROUP order by ID_KEY";
            string sql2 = "select min(ID_KEY) as ID_KEY,UGROUP from WEBUSER group by UGROUP order by ID_KEY";

            DataTable dt;

            if (rlDBType == "SQL")
            {
                dt = DBsql.RunDataTable(sql1, out errMsg);
            }
            else
            {
                dt = DBdb2.RunDataTable(sql2, out errMsg);
            }
            return dt;
        }

        public bool InsertRole(string roleName, out string errMsg)
        {
            errMsg = "";
            bool flag = false;

            this.init();

            string sql1 = "select * from WebUser where UGROUP='" + roleName + "'";
            string sql2 = "select * from WEBUSER where UGROUP='" + roleName + "'";

            DataTable dt;

            if (rlDBType == "SQL")
            {
                dt = DBsql.RunDataTable(sql1, out errMsg);
            }
            else
            {
                dt = DBdb2.RunDataTable(sql2, out errMsg);
            }
            if (dt.Rows.Count == 0)
            {
                sql1 = "insert into WebUser (UGROUP) values ('" + roleName + "')";
                sql2 = "insert into WEBUSER (UGROUP) values ('" + roleName + "')";
                if (rlDBType == "SQL")
                {
                    DBsql.RunNonQuery(sql1, out errMsg);
                }
                else
                {
                    DBdb2.RunNonQuery(sql2, out errMsg);
                }
                if (errMsg == "")
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
        }

        public bool UpdateRole(string roleId, string roleName, out string errMsg)
        {
            errMsg = "";
            this.init();
            string sql1 = "update WebUser set UGROUP='" + roleName + "' where UGROUP='" + roleId + "'";
            string sql2 = "update WEBUSER set UGROUP='" + roleName + "' where UGROUP='" + roleId + "'";
            if (rlDBType == "SQL")
            {
                DBsql.RunNonQuery(sql1, out errMsg);
            }
            else
            {
                DBdb2.RunNonQuery(sql2, out errMsg);
            }
            if (errMsg == "")
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool DeleteRoleByNM(string rolename, out string errMsg)
        {
            errMsg = "";
            bool flag = true;
            this.init();
            string sql1 = "select UID from WebUser where UGROUP='" + rolename + "'";
            string sql2 = "select UID from WEBUSER where UGROUP='" + rolename + "'";
            DataTable dt;
            if (rlDBType == "SQL")
            {
                dt = DBsql.RunDataTable(sql1, out errMsg);
            }
            else
            {
                dt = DBdb2.RunDataTable(sql2, out errMsg);
            }
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (dt.Rows[i]["UID"].ToString() != "")
                {
                    flag = false;
                    break;
                }
                else
                {
                    flag = true;
                }
            }
            if (flag == true)
            {
                sql1 = "delete from WebUser where UGROUP='" + rolename + "'";
                sql2 = "delete from WEBUSER where UGROUP='" + rolename + "'";
                if (rlDBType == "SQL")
                {
                    DBsql.RunNonQuery(sql1, out errMsg);
                }
                else
                {
                    DBdb2.RunNonQuery(sql2, out errMsg);
                }
                if (errMsg == "")
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
        }

        public DataTable GetUsersByRoleName(string rolename, out string errMsg)
        {
            errMsg = "";
            this.init();
            string sql1 = "select ID_KEY,UID,UGROUP from WebUser where UGROUP='" + rolename + "'and UID is not null";
            string sql2 = "select ID_KEY,UID,UGROUP from WEBUSER where UGROUP='" + rolename + "'and UID is not null";
            DataTable dt;
            if (rlDBType == "SQL")
            {
                dt = DBsql.RunDataTable(sql1, out errMsg);
            }
            else
            {
                dt = DBdb2.RunDataTable(sql2, out errMsg);
            }
            return dt;
        }

        public bool InsertUser(string username, string password, string rolename, out string errMsg)
        {
            errMsg = "";
            this.init();
            string sql1 = "select * from WebUser where UID='" + username + "' and UGROUP='" + rolename + "'";
            string sql2 = "select * from WEBUSER where UID='" + username + "' and UGROUP='" + rolename + "'";
            DataTable dt;
            if (rlDBType == "SQL")
            {
                dt = DBsql.RunDataTable(sql1, out errMsg);
            }
            else
            {
                dt = DBdb2.RunDataTable(sql2, out errMsg);
            }
            if (dt.Rows.Count == 0)
            {
                sql1 = "insert into WebUser (UID,PWD,UGROUP) values ('" + username + "','" + password + "','" + rolename + "')";
                sql2 = "insert into WEBUSER (UID,PWD,UGROUP) values ('" + username + "','" + password + "','" + rolename + "')";
                if (rlDBType == "SQL")
                {
                    DBsql.RunNonQuery(sql1, out errMsg);
                }
                else
                {
                    DBdb2.RunNonQuery(sql2, out errMsg);
                }
                if (errMsg == "")
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
        }

        public bool DeleteUserById(string idkey, out string errMsg)
        {
            errMsg = "";
            int id = int.Parse(idkey);
            this.init();
            string sql1 = "delete from WebUser where ID_KEY=" + id + "";
            string sql2 = "delete from WEBUSER where ID_KEY=" + id + "";
            if (rlDBType == "SQL")
            {
                DBsql.RunNonQuery(sql1, out errMsg);
            }
            else
            {
                DBdb2.RunNonQuery(sql2, out errMsg);
            }
            if (errMsg == "")
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public string GetOldPwd(string username, string rolename, out string errMsg)
        {
            errMsg = "";
            this.init();
            string sql1 = "select PWD from WebUser where UID='" + username + "' and UGROUP='" + rolename + "'";
            string sql2 = "select PWD from WEBUSER where UID='" + username + "' and UGROUP='" + rolename + "'";
            DataTable dt;
            if (rlDBType == "SQL")
            {
                dt = DBsql.RunDataTable(sql1, out errMsg);
            }
            else
            {
                dt = DBdb2.RunDataTable(sql2, out errMsg);
            }
            string oldpwd = dt.Rows[0]["PWD"].ToString();
            return oldpwd;
        }

        public bool EditPwd(string username, string rolename, string pwd, out string errMsg)
        {
            errMsg = "";
            this.init();
            string sql1 = "update WebUser set PWD='" + pwd + "' where UID='" + username + "' and UGROUP='" + rolename + "'";
            string sql2 = "update WEBUSER set PWD='" + pwd + "' where UID='" + username + "' and UGROUP='" + rolename + "'";
            if (rlDBType == "SQL")
            {
                DBsql.RunNonQuery(sql1, out errMsg);
            }
            else
            {
                DBdb2.RunNonQuery(sql2, out errMsg);
            }
            if (errMsg == "")
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public string GetRoleNameById(string idkey, out string errMsg)
        {
            errMsg = "";
            int id = int.Parse(idkey);
            this.init();
            string sql1 = "select UGROUP from WebUser where ID_KEY=" + id + "";
            string sql2 = "select UGROUP from WEBUSER where ID_KEY=" + id + "";
            DataTable dt;
            if (rlDBType == "SQL")
            {
                dt = DBsql.RunDataTable(sql1, out errMsg);
            }
            else
            {
                dt = DBdb2.RunDataTable(sql2, out errMsg);
            }
            string rolename = dt.Rows[0]["UGROUP"].ToString();
            return rolename;
        }

        private string init()
        {
            rlDBType = IniHelper.ReadIniData("RelationDBbase", "DBType", null);
            rtDBType = IniHelper.ReadIniData("RTDB", "DBType", null);
            pGl1 = IniHelper.ReadIniData("Report", "FH1", null);
            pGl2 = IniHelper.ReadIniData("Report", "FH2", null);

            return rlDBType;
        }

        public string GetDBtype()
        {
            this.init();
            string DBtype = rlDBType;
            return DBtype;
        }

        public bool upMenu(string sql, out string message)
        {
            return SAC.DB2.DBdb2.RunNonQuery(sql, out message);
        }



        //public static ArrayList RetSGLRDAY(string date, string[] point, out string errMsg)
        //{
        //    string sql;

        //    ArrayList list = new ArrayList();
        //    string[] val = new string[45];

        //    errMsg = "";

        //    for (int i = 0; i < point.Length; i++)
        //    {
        //        if (point[i] != null)
        //        {
        //            sql = "select * from 日统计数据表 where 时间='" + date + "' and 参数名称='" + point[i] + "'";

        //            DataTable dt = DBsql.RunDataTable(sql, out errMsg);

        //            if (dt.Rows.Count > 0 && dt != null)
        //            {
        //                for (int j = 0; j < dt.Rows.Count; j++)
        //                {
        //                    val[i] = dt.Rows[j]["参数值"].ToString();
        //                }
        //            }
        //            else
        //            {
        //                val[i] = "";
        //            }
        //        }
        //    }

        //    list.Add(point);
        //    list.Add(val);

        //    return list;
        //}

        //public static bool SaveSGLRDAY(string[] sapoint, string[] val, string date, out string errMsg)
        //{
        //    object obj;
        //    string sql;

        //    bool flag = false;

        //    errMsg = "";

        //    DBsql db = new DBsql();

        //    for (int i = 1; i < sapoint.Length; i++)
        //    {
        //        if (sapoint[i] != null)
        //        {
        //            if (val[i].ToString() != "")
        //            {
        //                sql = "select * from 日统计数据表 where 参数名称='" + sapoint[i] + "' and 时间='" + date + "' ";

        //                obj = DBsql.RunSingle(sql, out errMsg);

        //                if (obj == null)
        //                {
        //                    sql = "insert into  日统计数据表 (参数名称,参数值,时间) values ('" + sapoint[i] + "','" + val[i] + "','" + date + "')";
        //                    //DBsql.RunCommand(sql, out errMsg);
        //                    DBsql.RunNonQuery(sql, out errMsg);
        //                }
        //                else
        //                {
        //                    sql = "update 日统计数据表 set 参数值 = '" + val[i] + "' where 时间='" + date + "' and 参数名称='" + sapoint[i] + "' ";
        //                    //DBsql.RunCommand(sql, out errMsg);
        //                    DBsql.RunNonQuery(sql, out errMsg);
        //                }
        //            }
        //        }
        //    }

        //    if (errMsg == "")
        //        flag = true;

        //    return flag;
        //}


        //public static ArrayList RetSGLRBZ(string date, string[] point, string BZ, out string errMsg)
        //{
        //    string sql;

        //    ArrayList list = new ArrayList();
        //    string[] val = new string[45];

        //    errMsg = "";

        //    for (int i = 0; i < point.Length; i++)
        //    {
        //        if (point[i] != null)
        //        {
        //            sql = "select * from 班值统计数据表 where 时间='" + date + "' and 参数名称='" + point[i] + "' and 班组='" + BZ + "'";

        //            DataTable dt = DBsql.RunDataTable(sql, out errMsg);

        //            if (dt.Rows.Count > 0 && dt != null)
        //            {
        //                for (int j = 0; j < dt.Rows.Count; j++)
        //                {
        //                    val[i] = dt.Rows[j]["参数值"].ToString();
        //                }
        //            }
        //            else
        //            {
        //                val[i] = "";
        //            }
        //        }
        //    }

        //    list.Add(point);
        //    list.Add(val);

        //    return list;
        //}

        //public static bool SaveSGLRBZ(string[] arrpoint, string[] val, string date, string BZ, out string errMsg)
        //{
        //    object obj;
        //    string sql;

        //    bool flag = false;

        //    errMsg = "";

        //    DBsql db = new DBsql();

        //    for (int i = 1; i < arrpoint.Length; i++)
        //    {
        //        if (arrpoint[i] != null)
        //        {
        //            if (val[i].ToString() != "")
        //            {
        //                sql = "select * from 班值统计数据表 where 参数名称='" + arrpoint[i] + "' and 时间='" + date + "' and 班组='" + BZ + "'";

        //                obj = DBsql.RunSingle(sql, out errMsg);

        //                if (obj == null)
        //                {
        //                    sql = "insert into  班值统计数据表 (参数名称,参数值,时间,班组) values ('" + arrpoint[i] + "','" + val[i] + "','" + date + "','" + BZ + "')";
        //                    //DBsql.RunCommand(sql, out errMsg);
        //                    DBsql.RunNonQuery(sql, out errMsg);
        //                }
        //                else
        //                {
        //                    sql = "update 班值统计数据表 set 参数值 = '" + val[i] + "' where 时间='" + date + "' and 参数名称='" + arrpoint[i] + "' and 班组='" + BZ + "' ";
        //                    //DBsql.RunCommand(sql, out errMsg);
        //                    DBsql.RunNonQuery(sql, out errMsg);
        //                }
        //            }
        //        }
        //    }

        //    if (errMsg == "")
        //        flag = true;

        //    return flag;

        //}

        //public static string RetBZ(string TimeBZ, out string errMsg)
        //{
        //    string sql;
        //    string BZ = "";
        //    errMsg = "";

        //    sql = "select 班组编号 from 排班表 where '" + TimeBZ + "' between 起始时间 and 结束时间";

        //    object obj = DBsql.RunSingle(sql, out errMsg);
        //    if (obj != null && obj.ToString() != "")
        //    {
        //        BZ = obj.ToString();
        //    }

        //    return BZ;

        //}

        //public static ArrayList RetSBJL(string date, string JZ, out string errMsg)
        //{
        //    string sql;
        //    ArrayList listJL = new ArrayList();
        //    string[] jl1 = new string[45];
        //    string[] jl2 = new string[45];
        //    string[] jl3 = new string[45];
        //    errMsg = "";

        //    sql = "select * from 设备启停记录表 where 设备ID like '" + JZ + "%' and ( 起始时间 between timestamp('" + date + "-00.00.01.000000') and timestamp('" + date + "-23.59.59.999999') or 结束时间 between timestamp('" + date + "-00.00.01.000000') and timestamp('" + date + "-23.59.59.999999') )";

        //    DataTable dt = DBdb2.RunDataTable(sql, out errMsg);

        //    if (dt.Rows.Count > 0 && dt != null)
        //    {
        //        for (int i = 0; i < dt.Rows.Count; i++)
        //        {
        //            jl1[i] = dt.Rows[i]["起始时间"].ToString();
        //            jl2[i] = dt.Rows[i]["结束时间"].ToString();
        //            jl3[i] = dt.Rows[i]["设备ID"].ToString();
        //        }
        //    }
        //    else
        //    {
        //        jl1 = null;
        //        jl2 = null;
        //        jl3 = null;
        //    }

        //    listJL.Add(jl1);
        //    listJL.Add(jl2);
        //    listJL.Add(jl3);

        //    return listJL;
        //}


        //public static ArrayList RetSBNM(string[] jl3, out string errMsg)
        //{
        //    string sql;
        //    ArrayList list = new ArrayList();
        //    string[] val = new string[45];
        //    errMsg = "";
        //    if (jl3 != null)
        //    {
        //        for (int i = 0; i < jl3.Length; i++)
        //        {
        //            if (jl3[i] != null)
        //            {
        //                sql = "select * from 设备启停测点表 where 设备ID='" + jl3[i] + "'";
        //                DataTable dt = DBdb2.RunDataTable(sql, out errMsg);

        //                if (dt.Rows.Count > 0 && dt != null)
        //                {
        //                    for (int j = 0; j < dt.Rows.Count; j++)
        //                    {
        //                        val[i] = dt.Rows[j]["设备名称"].ToString();
        //                    }
        //                }
        //            }
        //            else
        //            {
        //                val[i] = "";
        //            }
        //        }
        //    }

        //    list.Add(val);
        //    list.Add(jl3);

        //    return list;
        //}     
    }
}
